package com.wondersgroup.fjzlpt.org.dao.impl.jtqy;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.quick.framework.boot.repository.sql.dialect.DialectProxy;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.alibaba.fastjson.JSONArray;
import com.quick.framework.boot.repository.sql.bean.SQLBean;
import com.quick.framework.boot.repository.sql.bean.table.TableBean;
import com.quick.framework.boot.repository.sql.jdbcTemplate.impl.BaseJTDAOImpl;
import com.wondersgroup.fjzlpt.org.dao.inter.jtqy.CygxDAO;
/**
 * 家庭成员关系Dao实现类
 * @author 01053853
 *
 */
@Repository
public class CygxDAOImpl extends BaseJTDAOImpl implements CygxDAO {

	@Autowired
	private DialectProxy dialectProxy;

	@Override
	@Autowired
	@Qualifier("TB_JTQY_CYGX")
	public void setTb(TableBean tb) {
		super.setTb(tb);
	}
	/**
	 * 查詢成员关系对象列表
	 * @param map
	 * @return
	 */
	@Override
	public JSONArray queryList(Map<String, String> map) {
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		JSONArray jsonArray = null;
		if(map!=null && map.size()>0){
			StringBuffer sql = new StringBuffer("SELECT A.XM,A.sfzh,A.CYGXDM,A.RYSX,B.JMXXID,C.XMMC,G.FWBMC QYXX FROM TB_JTQY_CYGX A  \n");
			sql.append(" LEFT JOIN TB_JTQY_JMXX B  ON A.SFZH = B.ZJHM  \n");
			sql.append(" LEFT JOIN TB_ZY_JTCYGX C ON A.CYGXDM = C.XMDM  \n");
			if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
				sql.append(" LEFT JOIN (SELECT D.JMXXID , GROUP_CONCAT(CONCAT(F.FWBMC,CASE WHEN E.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  )) FWBMC \n");
			}else if("LISTAGG".equals(groupFunName.toUpperCase())){
				sql.append(" LEFT JOIN (SELECT D.JMXXID , LISTAGG(CONCAT(F.FWBMC,CASE WHEN E.FWBSX = '1' THEN '(个人)' ELSE '(家庭)' END  ),',') WITHIN GROUP(order by null) FWBMC \n");
			}else{
				throw new RuntimeException("无法确定分组函数");
			}

			sql.append(" FROM TB_JTQY_QYXX D , TB_JTQY_FWBGX E , TB_XXWH_FWBJBXX F  \n");
			sql.append(" WHERE E.FWBID = F.FWBID  AND D.QYXXID = E.QYXXID  \n");
			sql.append(" and D.QYLX = '01'  GROUP BY D.JMXXID ) G  \n");
			sql.append(" ON B.JMXXID = G.JMXXID  \n");
			sql.append(" where 1 = 1 \n"); 
			List list = new ArrayList();
			if(!StringUtils.isEmpty((String)map.get("JMXXID")))
			{
				sql.append("and A.JMXXID=?");
				list.add(map.get("JMXXID"));
			}
			SQLBean sqlBean = new SQLBean(sql.toString(),list.toArray());
			jsonArray= super.queryList(sqlBean);
		}
		return jsonArray;
	}
	
	/**
	 * 通过居民信息id查询该患者的家庭成员的签约信息
	 * @param jmxxid
	 * @return
	 */
	public JSONArray queryJtcyQyxxByJmxxid(String jmxxid){
		String groupFunName = dialectProxy.getRow2ColumnFuncName(this.getJdbcTemp().getDataSource());
		StringBuffer sqlBuf = new StringBuffer();
		sqlBuf.append("select a.CYGXDM,a.XM,c.QYXXID,\n");
		//成员关系名称
		sqlBuf.append("(select XMMC from TB_ZY_JTCYGX where XMDM=a.CYGXDM) CYGXMC,");
		//成员签约的团队名称
		sqlBuf.append("(select TDMC from TB_TDGL_TDJBXX where TDID=c.YLTDID) QYTDMC,\n");
		//成员签约服务包
		if("GROUP_CONCAT".equals(groupFunName.toUpperCase())){
			sqlBuf.append("(select GROUP_CONCAT(e.FWBMC) from TB_JTQY_FWBGX d left join TB_XXWH_FWBJBXX e on d.FWBID=e.FWBID where d.QYXXID=c.QYXXID) QYFWB \n");
		}else if("LISTAGG".equals(groupFunName.toUpperCase())){
			sqlBuf.append("(select LISTAGG(e.FWBMC,',') WITHIN GROUP(order by null) from TB_JTQY_FWBGX d left join TB_XXWH_FWBJBXX e on d.FWBID=e.FWBID where d.QYXXID=c.QYXXID) QYFWB \n");
		}else{
			throw new RuntimeException("无法确定分组函数");
		}
		sqlBuf.append("from TB_JTQY_CYGX a left join TB_JTQY_JMXX b on a.SFZH=b.ZJHM \n");
		sqlBuf.append("left join TB_JTQY_QYXX c on b.JMXXID=c.JMXXID \n");
		sqlBuf.append("where (c.QYLX not in('00','05','06') or c.QYLX is null) and a.jmxxid = ?");
		
		SQLBean bean = new SQLBean(sqlBuf.toString(),jmxxid);
		return super.queryList(bean);
	}

	public void deleteByJmxxId(String jmxxId){
		StringBuilder sql = new StringBuilder("delete from ")
				.append(this.tb.getTableName()).append(" where 1=1 \n")
				.append("and JMXXID=?");
		SQLBean sb = new SQLBean(sql.toString(), new Object[]{jmxxId});
		this.update(sb);
	}
}
